package gmusic.ma.dao.impl;

import gmusic.ma.bo.ArtistMABean;
import gmusic.ma.bo.DemandeChargement;
import gmusic.ma.bo.ReleaseMABean;
import gmusic.ma.bo.TrackMABean;
import gmusic.ma.dao.MetalArchivesDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import algutil.connexion.GestionConnexion;
import algutil.date.ConversionDate;
import dao.QueryFilter;
import dao.Sort;

public class MetalArchivesDAOImpl implements MetalArchivesDAO {

	private static final Logger log = Logger
			.getLogger(MetalArchivesDAOImpl.class);

	private static MetalArchivesDAOImpl instance;

	public static MetalArchivesDAOImpl getInstance() {

		if (instance == null)
			instance = new MetalArchivesDAOImpl();
		return instance;
	}

	public boolean ajouter(ArtistMABean ab) throws SQLException {

		// Ajout de l'artiste en BD
		if (!artisteMAExists(ab.getMaid())) {
			Connection c = GestionConnexion.getConnexionGMusic();
			log.info("Insersion en BD de l'artiste " + ab);
			PreparedStatement p = c
					.prepareStatement("INSERT INTO ma_artist (maid, nom, pays_id, url_logo, statut, genre, lyrical_theme, creation_date, last_update_date) VALUES (?, ?, ?, ?, ?, ?, ?, now(), now())");
			p.setLong(1, ab.getMaid());
			p.setString(2, ab.getNom());
			p.setInt(3, ab.getPaysMaid());
			p.setString(4, ab.getURLLogo());
			p.setInt(5, ab.getStatut());
			p.setString(6, ab.getGenre());
			p.setString(7, ab.getLyricalTheme());
			p.executeUpdate();
			p.close();

			return true;

		} else {
			log.debug("L'artiste existe deja en BD : " + ab);
			updateArtistStatus(ab);
			return false;
		}
	}

	public void updateArtistStatus(ArtistMABean ab) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		log.info("Update du statut de l'artiste " + ab);
		PreparedStatement p = c
				.prepareStatement("UPDATE ma_artist SET statut=?, nom=?, pays_id=?, url_logo=?, genre=?, lyrical_theme=?, last_update_date=now() WHERE maid=?");
		p.setInt(1, ab.getStatut());
		p.setString(2, ab.getNom());
		p.setInt(3, ab.getPaysMaid());
		p.setString(4, ab.getURLLogo());
		p.setString(5, ab.getGenre());
		p.setString(6, ab.getLyricalTheme());
		p.setLong(7, ab.getMaid());
		p.executeUpdate();
		p.close();
	}

	public void ajoutHistorique(Object bean) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("INSERT INTO ma_recup_histo (maid, type, creation_date) VALUES (?, ?, now())");
		if (bean instanceof ArtistMABean) {
			p.setLong(1, ((ArtistMABean) bean).getMaid());
			p.setString(2, "A");
		} else if (bean instanceof ReleaseMABean) {
			p.setLong(1, ((ReleaseMABean) bean).getMaid());
			p.setString(2, "R");
		}
		p.executeUpdate();
		p.close();
	}

	/** @deprecated use add(ReleaseMABean rb) */
	public boolean ajouter(ReleaseMABean rb) throws SQLException {
		return add(rb);
	}

	public boolean add(ReleaseMABean rb) throws SQLException {

		Connection c = GestionConnexion.getConnexionGMusic();
		log.debug("Insertion en BD de la release " + rb);
		PreparedStatement p = c
				.prepareStatement("INSERT INTO ma_release (maid, nom, groupe_maid, annee, type_id, url_cover, url, review, creation_date, last_update_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, now(), now())");
		p.setLong(1, rb.getMaid());
		p.setString(2, rb.getNom());
		p.setLong(3, rb.getArtistMaId());
		p.setInt(4, rb.getAnnee());
		p.setInt(5, rb.getTypeId());
		p.setString(6, rb.getURLCoverRelative());
		p.setString(7, rb.getUrlRelative());
		p.setString(8, rb.getReview());
		p.executeUpdate();

		p.close();
		return true;
	}

	public boolean update(TrackMABean tb) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("UPDATE ma_track SET maid=?, nom=?, duree=?, last_update_date=now() WHERE release_maid=? AND disc=? AND num=?");
		if (tb.getMaid() != -1) {
			p.setLong(1, tb.getMaid());
		} else {
			p.setNull(1, Types.INTEGER);
		}
		p.setString(2, tb.getNom());
		if (tb.getDuree() != -1) {
			p.setInt(3, tb.getDuree());
		} else {
			p.setNull(3, Types.INTEGER);
		}
		p.setLong(4, tb.getReleaseMaid());
		p.setInt(5, tb.getDisc());
		p.setInt(6, tb.getNumero());
		p.executeUpdate();
		p.close();
		return true;
	}

	public boolean update(ReleaseMABean rb) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("UPDATE ma_release SET nom=?, groupe_maid=?, annee=?, type_id=?, url_cover=?, url=?, review=?, last_update_date=now() WHERE maid=?");
		p.setString(1, rb.getNom());
		p.setLong(2, rb.getArtistMaId());
		p.setInt(3, rb.getAnnee());
		p.setInt(4, rb.getTypeId());
		if (rb.getURLCoverRelative() != null) {
			p.setString(5, rb.getURLCoverRelative());
		} else {
			p.setNull(5, Types.VARCHAR);
		}
		if (rb.getUrlRelative() != null) {

			if (rb.getUrlRelative().length() > 400) {
				log.info(" TROP LOOOONNNGGG...->"
						+ rb.getUrlRelative().length() + " "
						+ rb.getUrlRelative());
			}
			p.setString(6, rb.getUrlRelative());
		} else {
			p.setNull(6, Types.VARCHAR);
		}
		if (rb.getReview() != null) {
			p.setString(7, rb.getReview());
		} else {
			p.setNull(7, Types.VARCHAR);
		}
		p.setLong(8, rb.getMaid());
		p.executeUpdate();
		p.close();
		return true;
	}

	/** @deprecated */
	public boolean ajouter(TrackMABean tb) throws SQLException {
		return add(tb);
	}

	public boolean add(TrackMABean tb) throws SQLException {

		if (!trackMAExists(tb)) {
			Connection c = GestionConnexion.getConnexionGMusic();
			log.info("Insertion en BD de la chanson " + tb);
			PreparedStatement p = c
					.prepareStatement("INSERT INTO ma_track (maid, release_maid, disc, num, nom, duree, lyric, creation_date) VALUES (?, ?, ?, ?, ?, ?, ?, now())");
			p.setLong(1, tb.getMaid());
			p.setLong(2, tb.getReleaseMaid());
			p.setInt(3, tb.getDisc());
			p.setInt(4, tb.getNumero());
			p.setString(5, tb.getNom());
			p.setInt(6, tb.getDuree());
			p.setString(7, tb.getLyrics());
			p.executeUpdate();
			p.close();
			return true;
		} else {
			log.info("La chanson existe deja en BD : " + tb);
			return false;
		}
	}

	public int getPaysMaid(String nomPays) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_pays WHERE nom=?");
		p.setString(1, nomPays);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			int paysId = r.getInt(1);
			log.debug("L'id du pays '" + nomPays + "' : " + paysId);
			return paysId;
		} else {
			log.error("Erreur - L'id du pays '" + nomPays
					+ "' n'a pas ete trouve!");
			return -1;
		}
	}

	public int getMAReleaseTypeId(String nomType) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT ty_id FROM type WHERE ty_nom=?");
		p.setString(1, nomType);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			int typeId = r.getInt(1);
			log.debug("L'id du type '" + nomType + "' : " + typeId);
			return typeId;
		} else {
			log.error("Erreur - L'id du type '" + nomType
					+ "' n'a pas ete trouve!");
			return -1;
		}
	}

	public boolean artisteMAExists(long maid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_artist WHERE maid=?");
		p.setLong(1, maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			return true;
		} else {
			return false;
		}
	}

	public boolean releaseMAExists(long maid) throws SQLException {

		boolean alreadyLoads;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_release WHERE maid=?");
		p.setLong(1, maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			alreadyLoads = true;
		} else {
			alreadyLoads = false;
		}
		r.close();
		p.close();

		return alreadyLoads;
	}

	public boolean releaseAndTracksAlreadyLoad(long rmaid) throws SQLException {

		boolean alreadyLoads;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_recup_histo WHERE maid=? AND type='R'");
		p.setLong(1, rmaid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			alreadyLoads = true;
		} else {
			alreadyLoads = false;
		}
		r.close();
		p.close();

		return alreadyLoads;
	}

	public boolean trackMAExists(TrackMABean tb) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_track WHERE release_maid=? and disc=? and num=?");
		p.setLong(1, tb.getReleaseMaid());
		p.setInt(2, tb.getDisc());
		p.setInt(3, tb.getNumero());
		ResultSet r = p.executeQuery();
		if (r.next()) {
			return true;
		} else {
			return false;
		}
	}

	/** @deprecated */
	public boolean trackMAExists(long maid) throws SQLException {
		try {
			new Long(maid);
		} catch (Exception e) {
			// maid null
			return false;
		}
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_track WHERE maid=?");
		p.setLong(1, maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			return true;
		} else {
			return false;
		}
	}

	public List<ArtistMABean> rechercherArtistes(String nom)
			throws SQLException {
		List<ArtistMABean> artistes = new ArrayList<ArtistMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_artist WHERE nom like ?");
		p.setString(1, "%" + nom + "%");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			artistes.add(new ArtistMABean(r));
		}

		return artistes;
	}

	public ReleaseMABean getRelease(long release_maid) throws SQLException {
		ReleaseMABean rb = null;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_release WHERE maid=?");
		p.setLong(1, release_maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			rb = new ReleaseMABean(r);
		} else {
			log.error("La release n'a pas ete trouve " + release_maid);
		}

		return rb;
	}

	public ReleaseMABean getMAReleaseAndTracks(long release_maid)
			throws SQLException {

		ReleaseMABean rb = getRelease(release_maid);
		if (rb != null) {
			rb.setTracksMA(getTracks(release_maid));
		}

		return rb;
	}

	public List<TrackMABean> getTracks(long release_maid) throws SQLException {
		List<TrackMABean> tracks = new ArrayList<TrackMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_track WHERE release_maid=?");
		p.setLong(1, release_maid);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			tracks.add(new TrackMABean(r));
		}

		if (tracks.size() == 0) {
			log.warn("La release n'a pas de tracks " + release_maid);
		}

		return tracks;
	}

	public TrackMABean getTrack(long rmaid, int discNumber, int trackNumber)
			throws SQLException {
		TrackMABean track = null;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_track WHERE release_maid=? AND disc=? AND num=?");
		p.setLong(1, rmaid);
		p.setInt(2, discNumber);
		p.setInt(3, trackNumber);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			track = new TrackMABean(r);
		}

		return track;
	}

	/** @deprecated */
	public List<ReleaseMABean> getMAReleases(long artist_maid)
			throws SQLException {
		List<ReleaseMABean> releases = new ArrayList<ReleaseMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_release WHERE groupe_maid=?");
		p.setLong(1, artist_maid);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			releases.add(new ReleaseMABean(r));
		}

		return releases;
	}

	public List<ReleaseMABean> getMAReleasesAndTracks(long amaid)
			throws SQLException {

		return getMAReleasesAndTracks(amaid, null);
	}

	public List<ReleaseMABean> getMAReleasesAndTracks(long amaid, Sort sort)
			throws SQLException {
		List<ReleaseMABean> releases = getReleases(amaid, sort);
		for (int i = 0; i < releases.size(); i++) {
			releases.get(i).setTracksMA(getTracks(releases.get(i).getMaid()));
		}
		return releases;
	}

	public List<ArtistMABean> getAllArtists() throws SQLException {
		List<ArtistMABean> artistes = new ArrayList<ArtistMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_artist ORDER BY nom");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			artistes.add(new ArtistMABean(r));
		}
		r.close();
		p.close();
		return artistes;
	}

	public ArtistMABean getArtist(long maid) throws SQLException {
		ArtistMABean artiste = null;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_artist WHERE maid=?");
		p.setLong(1, maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			artiste = new ArtistMABean(r);
		}

		return artiste;
	}

	public List<ReleaseMABean> rechercherReleases(String nom)
			throws SQLException {
		List<ReleaseMABean> releases = new ArrayList<ReleaseMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_release WHERE nom like ?");
		p.setString(1, "%" + nom + "%");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			releases.add(new ReleaseMABean(r));
		}

		return releases;
	}

	public List<TrackMABean> rechercherTracks(String nom) throws SQLException {
		List<TrackMABean> tracks = new ArrayList<TrackMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_track WHERE nom like ?");
		p.setString(1, "%" + nom + "%");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			tracks.add(new TrackMABean(r));
		}

		return tracks;
	}

	public int getPaysGmid(int paysMaid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT pa_id FROM pays WHERE maid=?");
		p.setInt(1, paysMaid);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			return r.getInt(1);
		} else {
			return -1;
		}
	}

	public int getNbDisc(long releaseMaid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT COUNT(DISTINCT disc) FROM ma_track WHERE release_maid=?");
		p.setLong(1, releaseMaid);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			return r.getInt(1);
		} else {
			return -1;
		}
	}

	public int getNbTracks(long releaseMaid, int discNum) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT COUNT(*) FROM ma_track WHERE release_maid=? AND disc=?");
		p.setLong(1, releaseMaid);
		p.setInt(1, discNum);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			return r.getInt(1);
		} else {
			return -1;
		}
	}

	public List<ReleaseMABean> getReleases(long amaid) throws SQLException {

		return getReleases(amaid, null);
	}

	public List<ReleaseMABean> getReleases(long amaid, Sort sort)
			throws SQLException {
		List<ReleaseMABean> releases = new ArrayList<ReleaseMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		String query = "SELECT * FROM ma_release WHERE groupe_maid=? "
				+ (sort != null ? sort.getSortQuery() : "");
		PreparedStatement p = c.prepareStatement(query);
		p.setLong(1, amaid);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			releases.add(new ReleaseMABean(r));
		}
		r.close();
		p.close();
		return releases;
	}

	public void supprimerArtiste(long artist_maid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("DELETE FROM ma_artist WHERE maid=?");
		p.setLong(1, artist_maid);
		p.executeUpdate();
		p.close();
	}

	public void supprimerRelease(long release_maid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("DELETE FROM ma_release WHERE maid=?");
		p.setLong(1, release_maid);
		p.executeUpdate();
		p.close();
	}

	public void supprimerTracks(long release_maid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("DELETE FROM ma_track WHERE release_maid=?");
		p.setLong(1, release_maid);
		p.executeUpdate();
		p.close();
	}

	public List<String> getPremieresLettreArtistes() throws SQLException {
		List<String> premL = new ArrayList<String>();

		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT DISTINCT SUBSTRING( nom, 1, 1 ) lettre FROM ma_artist");
		ResultSet r = p.executeQuery();

		while (r.next()) {
			premL.add(r.getString("lettre"));
		}

		r.close();
		p.close();

		return premL;
	}

	public int getNbArtists() throws SQLException {

		int nbGroupes = -1;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT count(*) FROM ma_artist");
		ResultSet r = p.executeQuery();

		if (r.next()) {
			nbGroupes = r.getInt(1);
		}

		r.close();
		p.close();

		return nbGroupes;
	}

	public int getNbReleases() throws SQLException {

		int nbReleases = -1;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT count(*) FROM ma_release");
		ResultSet r = p.executeQuery();

		if (r.next()) {
			nbReleases = r.getInt(1);
		}

		r.close();
		p.close();

		return nbReleases;
	}

	public int getNbTracks() throws SQLException {

		int nbTracks = -1;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT count(*) FROM ma_track");
		ResultSet r = p.executeQuery();

		if (r.next()) {
			nbTracks = r.getInt(1);
		}

		r.close();
		p.close();

		return nbTracks;
	}

	public List<DemandeChargement> getDemandesChargement(String statut)
			throws SQLException {

		return getDemandesChargement(statut, new QueryFilter());
	}

	/**
	 * @deprecated
	 */
	public List<DemandeChargement> getDemandesChargement(String statut,
			Sort sort) throws SQLException {

		return getDemandesChargement(statut, new QueryFilter(sort));
	}

	public List<DemandeChargement> getDemandesChargement(String statut,
			QueryFilter qf) throws SQLException {

		List<DemandeChargement> demandes = new ArrayList<DemandeChargement>();

		Connection c = GestionConnexion.getConnexionGMusic();

		String query = "SELECT * FROM ma_demande_recup WHERE statut=? "
				+ (qf != null ? qf.getSortQuery() : "") + " "
				+ (qf != null ? qf.getLimitQuery() : "");
		PreparedStatement p = c.prepareStatement(query);
		p.setString(1, statut);
		ResultSet r = p.executeQuery();

		while (r.next()) {
			demandes.add(new DemandeChargement(r));
		}

		r.close();
		p.close();

		return demandes;
	}

	public DemandeChargement getDemandeChargement(int demId)
			throws SQLException {

		DemandeChargement demande = null;

		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_demande_recup WHERE id=?");
		p.setInt(1, demId);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			demande = new DemandeChargement(r);
		}

		r.close();
		p.close();

		return demande;
	}

	public void ajouter(DemandeChargement dem) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		log.info("Insertion en BD de la demande " + dem);
		PreparedStatement p = c
				.prepareStatement("INSERT INTO ma_demande_recup (amaid, source, statut, priorite, creation_date) VALUES (?, ?, ?, ?, now())");
		p.setLong(1, dem.getAmaid());
		if (dem.getSource() != null) {
			p.setString(2, dem.getSource());
		} else {
			p.setNull(2, Types.VARCHAR);
		}
		p.setString(3, dem.getStatut());
		p.setInt(4, dem.getPriorite());
		p.executeUpdate();
		p.close();
	}

	public void supprimerDemandeChargement(int demId) throws SQLException {

		Connection c = GestionConnexion.getConnexionGMusic();
		log.info("Suppression en BD de la demande " + demId
				+ " (si elle n'est pas en cours)");
		PreparedStatement p = c
				.prepareStatement("DELETE FROM ma_demande_recup WHERE id=? and statut=?");
		p.setInt(1, demId);
		p.setString(2, DemandeChargement.TO_LOAD);
		p.executeUpdate();
		p.close();
	}

	public void modifierPrioriteDemandeChargement(int demId, int priorite)
			throws SQLException {

		Connection c = GestionConnexion.getConnexionGMusic();
		log.info("Modification de la priorite pour la demande " + demId
				+ " -->" + priorite + " (si elle n'est pas en cours)");
		PreparedStatement p = c
				.prepareStatement("UPDATE ma_demande_recup SET priorite=? WHERE id=? and statut=?");
		p.setInt(1, priorite);
		p.setInt(2, demId);
		p.setString(3, DemandeChargement.TO_LOAD);
		p.executeUpdate();
		p.close();
	}

	public DemandeChargement getDemandeChargementLoading(long amaid)
			throws SQLException {

		DemandeChargement demande = null;

		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_demande_recup WHERE amaid=? AND statut=?");
		p.setLong(1, amaid);
		p.setString(2, DemandeChargement.LOADING);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			demande = new DemandeChargement(r);
		}

		r.close();
		p.close();

		return demande;
	}

	public DemandeChargement getDemandeChargement(long amaid, String statut)
			throws SQLException {

		DemandeChargement demande = null;

		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_demande_recup WHERE amaid=? AND statut=?");
		p.setLong(1, amaid);
		p.setString(2, statut);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			demande = new DemandeChargement(r);
		}

		r.close();
		p.close();

		return demande;
	}

	public void majStatDemandeChargement(ReleaseMABean rb) throws SQLException {

		DemandeChargement dem = getDemandeChargementLoading(rb.getArtistMaId());
		if (dem != null) {
			Connection c = GestionConnexion.getConnexionGMusic();
			log
					.info("Modification du nombre de releases/tracks "
							+ dem.getId());
			PreparedStatement p = c
					.prepareStatement("UPDATE ma_demande_recup SET nb_ajout_releases=?, nb_ajout_tracks=? WHERE id=? and statut=?");
			p.setInt(1, dem.getNbAjoutReleases() + 1);
			p.setInt(2, dem.getNbAjoutTracks() + rb.getTracksMA().size());
			p.setInt(3, dem.getId());
			p.setString(4, DemandeChargement.LOADING);
			p.executeUpdate();
			p.close();
		}
	}

	public void alimenterNbReleasesToLoad(DemandeChargement dem)
			throws SQLException {

		List<ReleaseMABean> releases = dem.getArtist().getReleasesMA();
		if (releases != null) {
			int nbRealeasesToLoad = 0;
			for (int i = 0; i < releases.size(); i++) {
				if (!releaseMAExists(releases.get(i).getMaid())) {
					nbRealeasesToLoad++;
				}
			}

			if (nbRealeasesToLoad > 0) {
				Connection c = GestionConnexion.getConnexionGMusic();
				log.info("Modification du nombre de releases a charger "
						+ dem.getId());
				PreparedStatement p = c
						.prepareStatement("UPDATE ma_demande_recup SET nb_releases_to_load=? WHERE id=?");
				p.setInt(1, nbRealeasesToLoad);
				p.setInt(2, dem.getId());
				p.executeUpdate();
				p.close();
			} else {
				Connection c = GestionConnexion.getConnexionGMusic();
				log.info("Modification du nombre a charger " + dem.getId());
				PreparedStatement p = c
						.prepareStatement("UPDATE ma_demande_recup SET nb_releases_to_load=?, statut=? WHERE id=?");
				p.setInt(1, nbRealeasesToLoad);
				p.setString(2, DemandeChargement.LOADED);
				p.setInt(3, dem.getId());
				p.executeUpdate();
				p.close();
			}
		}
	}

	public List<DemandeChargement> getDemandesChargementToPreLoad()
			throws SQLException {
		List<DemandeChargement> demandes = new ArrayList<DemandeChargement>();

		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_demande_recup WHERE statut=? AND nb_releases_to_load IS NULL");
		p.setString(1, DemandeChargement.TO_LOAD);
		ResultSet r = p.executeQuery();

		while (r.next()) {
			demandes.add(new DemandeChargement(r));
		}

		r.close();
		p.close();

		return demandes;
	}

	public void updateDemandeChargementStatut(DemandeChargement dem,
			String statut) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		log.info("Modification du statut de " + dem.getId());
		PreparedStatement p = c
				.prepareStatement("UPDATE ma_demande_recup SET statut=? WHERE id=?");
		p.setString(1, statut);
		p.setInt(2, dem.getId());
		p.executeUpdate();
		p.close();
	}

	public boolean isArtistAlreadyLoadThePreviousMonth(long amaid)
			throws SQLException {
		boolean alreadyLoad = false;

		Connection c = GestionConnexion.getConnexionGMusic();
		String previousMonth = ConversionDate.getAnnee4()
				+ (Integer.parseInt(ConversionDate.getMois()) - 1)
				+ ConversionDate.getJour();
		String query = "SELECT * FROM ma_demande_recup WHERE amaid=? AND statut=? AND creation_date>'"
				+ previousMonth + "'";
		PreparedStatement p = c.prepareStatement(query);
		query = query.replaceFirst("?", "" + amaid);
		query = query.replaceFirst("?", DemandeChargement.LOADED);
		log.debug("QRY : " + query);
		p.setLong(1, amaid);
		p.setString(2, DemandeChargement.LOADED);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			alreadyLoad = true;
		}

		r.close();
		p.close();

		return alreadyLoad;
	}

	public List<ArtistMABean> getArtistsToMAJ(int nbJours) throws SQLException {

		List<ArtistMABean> artistes = new ArrayList<ArtistMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_artist a WHERE statut=1 AND not exists (SELECT 1 FROM ma_demande_recup r WHERE a.maid=r.amaid and r.last_update_date>subdate(now(), ?)) AND not exists (SELECT 1 FROM ma_demande_recup r WHERE a.maid=r.amaid and statut in ('TO LOAD', 'LOADING'))");
		p.setInt(1, nbJours);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			artistes.add(new ArtistMABean(r));
		}
		r.close();
		p.close();
		return artistes;
	}

	public List<ArtistMABean> getArtistsByName(String name) throws SQLException {

		List<ArtistMABean> artistes = new ArrayList<ArtistMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_artist WHERE nom LIKE ?");
		p.setString(1, name);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			artistes.add(new ArtistMABean(r));
		}
		r.close();
		p.close();
		return artistes;
	}

	public List<ReleaseMABean> getReleasesByName(String name)
			throws SQLException {
		List<ReleaseMABean> releases = new ArrayList<ReleaseMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_release WHERE nom like ? ");
		p.setString(1, name);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			releases.add(new ReleaseMABean(r));
		}
		r.close();
		p.close();
		return releases;
	}

	public List<ReleaseMABean> getReleasesByName(long maid, String chaine)
			throws SQLException {
		List<ReleaseMABean> releases = new ArrayList<ReleaseMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_release WHERE groupe_maid=? AND nom like ? ");
		p.setLong(1, maid);
		p.setString(2, chaine);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			releases.add(new ReleaseMABean(r));
		}
		r.close();
		p.close();
		return releases;
	}

	public void updateLastMajDate(long amaid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("UPDATE ma_artist SET last_maj_date=now() WHERE maid=?");
		p.setLong(1, amaid);
		p.executeUpdate();
		p.close();
	}

	public List<ArtistMABean> getArtistsToMaj() throws SQLException {

		List<ArtistMABean> artistes = new ArrayList<ArtistMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_artist WHERE statut<>6 AND last_maj_date is null ORDER BY nom");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			artistes.add(new ArtistMABean(r));
		}
		r.close();
		p.close();
		return artistes;
	}
}
